﻿'Clinciu Andrei
Option Strict On

Imports System.Collections.Generic
Imports System.Data.OleDb
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Word
Imports System.Xml
Imports System.Xml.Linq


Public Class Order

    Private _id, _paymentTypeId, _perId As Integer
    Private _creationDate, _paymentTypeName, _personName As String
    Private _paid As Boolean
    Private _products As List(Of OrderProducts)
    Private _totalPrice As Decimal

    Private Sub New(ByVal id As Integer, ByVal personName As String, ByVal creationDate As String, ByVal paid As Boolean, ByVal paymentTypeId As Integer,
                    ByVal paymentTypeName As String, ByVal products As List(Of OrderProducts), ByVal totalPrice As Decimal, Optional ByVal perId As Integer = 0)
        _id = id
        _personName = personName
        _creationDate = creationDate
        _paid = paid
        _paymentTypeId = paymentTypeId
        _paymentTypeName = paymentTypeName
        _products = products
        _totalPrice = totalPrice
        _perId = perId
    End Sub
#Region "Propreties"


    Public ReadOnly Property Id As Integer
        Get
            Return _id
        End Get
    End Property
    Public Property CreationDate As String
        Get
            Return _creationDate
        End Get
        Set(ByVal value As String)
            _creationDate = value
        End Set
    End Property
    Public Property Paid As Boolean
        Get
            Return _paid
        End Get
        Set(ByVal value As Boolean)
            _paid = value
        End Set
    End Property
    Public Property PaymentTypeId As Integer
        Get
            Return _paymentTypeId
        End Get
        Set(ByVal value As Integer)
            _paymentTypeId = value
        End Set
    End Property
    Public Property PerId As Integer
        Get
            Return _perId
        End Get
        Set(ByVal value As Integer)
            _perId = value
        End Set
    End Property
    Public Property PaymentTypeName As String
        Get
            Return _paymentTypeName
        End Get
        Set(ByVal value As String)
            _paymentTypeName = value
        End Set
    End Property
    Public Property Products As List(Of OrderProducts)
        Get
            Return _products
        End Get
        Set(ByVal value As List(Of OrderProducts))
            _products = value
        End Set
    End Property
    Public Property PersonName As String
        Get
            Return _personName
        End Get
        Set(ByVal value As String)
            _personName = value
        End Set
    End Property
    Public Property TotalPrice As Decimal
        Get
            Return _totalPrice
        End Get
        Set(ByVal value As Decimal)
            _totalPrice = value
        End Set
    End Property

#End Region

    'The first ideea was to show everything in the listviewbox but it can't be multiline so only show a part of it:)
    Public Shared Function GetGiftListProd(ByRef dbh As DatabaseHelper, ByVal listId As Integer) As List(Of OrderGiftListProd)
        Dim theList As New List(Of OrderGiftListProd)

        Dim reader As OleDbDataReader = dbh.GetReader("SELECT ProdNaam,GLPProdId,GLPLijstId,GLPAantal from Product p, GeschenklijstProduct gp,GeschenkLijst gl    WHERE(gp.GLPProdId = p.ProdId And gl.LijstId = gp.GLPLijstId And gl.LijstId = ?)", {listId})
        Do While reader.Read()
            theList.Add(New OrderGiftListProd(CStr(reader("ProdNaam")), CInt(reader("GLPProdId")), CInt(reader("GLPLijstId")), CInt(reader("GLPAantal"))))
        Loop
        reader.Close()
        Return theList
    End Function

    'Products for edit
    Public Shared Function GetOrderProd(ByRef dbh As DatabaseHelper, ByVal bestId As Integer) As List(Of OrderGiftListProd)
        Dim theList As New List(Of OrderGiftListProd)
        Dim reader As OleDbDataReader = dbh.GetReader("SELECT ProdNaam,BGLPProdId,BGLPLijstId,BGLPAantal,GLPAantal from Product p, BestellingGeschenklijstProduct bgp,GeschenkLijst gl,GeschenklijstProduct gp   WHERE(bgp.BGLPProdId = p.ProdId And gl.LijstId = bgp.BGLPLijstId And gp.GLPLijstId = gl.LijstId) And gp.GLPProdId = bgp.BGLPProdId and bgp.BGLPBestId=?", {bestId})
        Do While reader.Read()
            theList.Add(New OrderGiftListProd(CStr(reader("ProdNaam")), CInt(reader("BGLPProdId")), CInt(reader("BGLPLijstId")), CInt(reader("GLPAantal")), CInt(reader("BGLPAantal"))))
        Loop
        reader.Close()
        Return theList
    End Function

    'use the same class & function from above only this time the totalOrdered is filled:)
    Public Shared Function GetOrdersFromDb(ByRef dbh As DatabaseHelper) As Dictionary(Of Integer, Order)
        Dim theList As New Dictionary(Of Integer, Order)
        Dim totalItems, bestId As Integer
        Dim subtotal As Decimal

        'Select everything from the 4 tabels and save them in a dictionary so we can ask the information back easily
        Dim reader As OleDbDataReader = dbh.GetReader("SELECT BestId,PerNaam,PerID,PerVoornaam,BestDatumAanmaak,BestBetaald,BestBetaaldTypeId,bt.BetaalTypeNaam,ProdNaam,BGLPAantal,  glp.GLPAantal, ProdPrijs FROM Bestelling b, BestellingGeschenklijstProduct bgp, Product p,prmBetaalType bt, Persoon per,GeschenklijstProduct glp  WHERE b.BestId = bgp.BGLPBestId AND bgp.BGLPProdId = p.ProdId and bt.BetaalTypeId = BestBetaaldTypeId and per.PerId = BestPerId and glp.GLPLijstId = bgp.BGLPLijstId and glp.GLPProdId = bgp.BGLPProdId", {})
        Do While reader.Read()
            totalItems = CInt(reader("BGLPAantal"))
            bestId = CInt(reader("BestId"))
            subtotal = CDec(reader("ProdPrijs")) * totalItems

            'New order products.. add it to a list and then add to the list again and again and again..
            Dim itemsAndPrice As New OrderProducts(CStr(reader("ProdNaam")), totalItems, CDec(reader("ProdPrijs")), subtotal, CInt(reader("GLPAantal")))
            Dim itemsList As New List(Of OrderProducts)
            itemsList.Add(itemsAndPrice)

            Dim order As New Order(bestId, CStr(reader("PerNaam")) + " " + CStr(reader("PerVoornaam")), CStr(reader("BestDatumAanmaak")), CBool(reader("BestBetaald")), CInt(reader("BestBetaaldTypeId")), CStr(reader("BetaalTypeNaam")), itemsList, subtotal, CInt(reader("PerID")))

            If theList.ContainsKey(bestId) Then
                Dim lastOrder As Order = theList.Item(bestId)
                lastOrder.Products.Add(itemsAndPrice)
                Dim newOrder As New Order(lastOrder.Id, lastOrder.PersonName, lastOrder.CreationDate, lastOrder.Paid, lastOrder.PaymentTypeId, lastOrder.PaymentTypeName, lastOrder.Products, lastOrder.TotalPrice + CDec(reader("ProdPrijs")) * totalItems, CInt(reader("PerID")))
                theList.Item(bestId) = newOrder
            Else
                theList.Add(bestId, order)
            End If

        Loop
        reader.Close()

        Return theList
    End Function

    'Add orders to db with transaction
    Public Shared Sub AddOrderToDb(ByRef db As DatabaseHelper, ByVal listId As Integer, ByVal products As List(Of OrderGiftListProd), ByVal owner As Person, ByVal payment As PaymentType)
        Dim transaction As OleDbTransaction = db.DBConnection.BeginTransaction()
        Dim bestId As Integer = 1
        Dim dateToday As String = Now.Day & "-" & Now.Month & "-" & Now.Year
        Try
            Dim reader As OleDbDataReader = db.GetReader("SELECT TOP 1 BestId from  Bestelling ORDER BY BestId desc ", {}, transaction)
            If reader.Read() Then
                bestId = CInt(reader("BestId"))
            End If
            reader.Close()
            'increase the reader by one
            bestId += 1

            db.ExecuteSQL("INSERT INTO Bestelling (BestId,BestPerId,BestDatumAanmaak,BestBetaald,BestBetaaldTypeId) VALUES (?,?,?,?,?)",
                           {bestId, owner.Id, dateToday, 0, payment.Id}, transaction)

            'add each insert in the transaction..
            For Each product As OrderGiftListProd In products
                '  [BGLPProdId]   ,[BGLPLijstId] ,[BGLPBestId],[BGLPAantal]
                db.ExecuteSQL("INSERT INTO BestellingGeschenklijstProduct VALUES(?, ?,?,? )", {product.ProdId, product.ListId, bestId, product.TotalOrdered}, transaction)
            Next

            transaction.Commit()
        Catch ex As Exception
            transaction.Rollback()
        End Try
    End Sub

    'delete an existing order
    Shared Sub DeleteOrder(ByRef db As DatabaseHelper, ByVal order As Order)
        Dim transaction As OleDbTransaction = db.DBConnection.BeginTransaction()
        Try
            'delete all of the products for this order
            db.ExecuteSQL("DELETE FROM  BestellingGeschenklijstProduct WHERE BGLPBestId = ?", {order.Id}, transaction)
            'and ONLY THEN delete it or you will get errors
            db.ExecuteSQL("DELETE FROM Bestelling WHERE BestId = ?", {order.Id}, transaction)
            transaction.Commit()
        Catch ex As Exception
            transaction.Rollback()
        End Try
    End Sub

    Shared Sub EditOrder(ByRef db As DatabaseHelper, ByVal orderId As Integer, ByVal list As List(Of OrderGiftListProd), ByVal paymentType As PaymentType, ByVal paid As Boolean)
        Dim transaction As OleDbTransaction = db.DBConnection.BeginTransaction()
        Dim dateToday As String = Now.Year & "/" & Now.Month & "/" & Now.Day
        Try
            db.ExecuteSQL("UPDATE Bestelling SET BestBetaald = ?,BestBetaaldTypeId =? WHERE BestId = ?", {paid, paymentType.Id, orderId}, transaction)

            'Update for each product
            ' [BGLPProdId]
            ',[BGLPLijstId]
            ',[BGLPBestId]
            ',[BGLPAantal]
            For Each product As OrderGiftListProd In list
                If product.TotalOrdered = 0 Then
                    'delete
                    db.ExecuteSQL("DELETE FROM BestellingGeschenklijstProduct WHERE BGLPProdId=? AND BGLPLijstId=? AND BGLPBestId=?", {product.ProdId, product.ListId, orderId}, transaction)
                Else
                    'update
                    db.ExecuteSQL("UPDATE BestellingGeschenklijstProduct SET BGLPAantal=? WHERE BGLPProdId=? AND BGLPLijstId=? AND BGLPBestId=?", {product.TotalOrdered, product.ProdId, product.ListId, orderId}, transaction)
                End If
            Next

            transaction.Commit()
        Catch ex As Exception
            transaction.Rollback()
        End Try
    End Sub

    Shared Sub ExportToWord(ByRef dbh As DatabaseHelper, ByVal file As String, ByVal order As Order, ByVal startupPath As String)
        Dim WordApp As Word.Application
        Dim doc As Word.Document

        Dim settingsPath As String = startupPath & "\settings.xml"
        Dim settings = XDocument.Load(settingsPath)
        Dim logo As String = settings.<settings>.<logo>.Value
        Dim imagesFolder As String = settings.<settings>.<imagesFolder>.Value
        Dim firmName As String = settings.<settings>.<firmName>.Value
        Dim btwNummer As String = settings.<settings>.<btwNummer>.Value
        Dim telefoon As String = settings.<settings>.<telefoon>.Value
        Dim plaats As String = settings.<settings>.<plaats>.Value

        If System.IO.Path.GetExtension(file) = "" Then
            file += ".doc"
        End If

        'Open word
        WordApp = New Word.Application

        '  doc = WordApp.Documents.Add
        doc = WordApp.Documents.Open(startupPath & "\templates\facturation.dotx")
        'Don't display allerts or errors and don't show the word app
        WordApp.Visible = False
        WordApp.DisplayAlerts = Word.WdAlertLevel.wdAlertsNone

        'facturation details
        'Dim person As Person
        ' Facturation.getFacturationForPerson(person, dbh)

        'select the logo range
        Dim rngimage As Word.Range = doc.Bookmarks("Logo").Range
        'add the logo 
        rngimage.InlineShapes.AddPicture(imagesFolder & "\" & logo)

        'write own information
        order.WriteBookmark("bedrijf", firmName, doc)
        order.WriteBookmark("adres", plaats, doc)
        order.WriteBookmark("telefoon", telefoon, doc)
        order.WriteBookmark("btw", btwNummer, doc)

        'information of facturation
        Dim pers As Person = Person.GetPersonFromDatabase(dbh, order.PerId)
        Dim facturatie As Facturation = Facturation.getFacturationForPerson(pers, dbh)

        order.WriteBookmark("klantadres", facturatie.AdresLijn1 & " " & facturatie.AdresLijn2, doc)
        order.WriteBookmark("klantbedrijf", facturatie.BedrijfsNaam & " " & pers.Naam & " " & pers.Voornaam, doc)
        order.WriteBookmark("klantgemeenteofland", facturatie.Plaats & " " & facturatie.Land, doc)
        order.WriteBookmark("klantpostcode", facturatie.PostCode, doc)

        'debugging purpose only
        '  For Each b As Word.Bookmark In doc.Bookmarks
        'Console.WriteLine(b.Name)
        ' Next

        Dim products As List(Of OrderProducts) = order.Products
        'create new table
        Dim tabel As Word.Table = doc.Tables.Add(doc.Bookmarks("tabel").Range, 1, 4)
        'add a style to the table
        tabel.Style = "Light Shading - Accent 3"
        'make the headers
        Dim row As Integer = 2
        tabel.Cell(1, 1).Range.Text = "Product"
        tabel.Cell(1, 2).Range.Text = "Aantal"
        tabel.Cell(1, 3).Range.Text = "Prijs per eenheid"
        tabel.Cell(1, 4).Range.Text = "Subtotaal"

        For Each prod As OrderProducts In products
            tabel.Rows.Add().Select()
            tabel.Cell(row, 1).Range.Text = prod.Product
            tabel.Cell(row, 2).Range.Text = CStr(prod.TotalOrdered)
            tabel.Cell(row, 3).Range.Text = CStr(prod.PricePerOne)
            tabel.Cell(row, 4).Range.Text = CStr(prod.Subtotal)
            row += 1
        Next

        order.WriteBookmark("totaleprijs", CStr(order.TotalPrice), doc)

        'Save, quit & close the word
        doc.SaveAs(CType(file, Object))
        WordApp.Quit()
        WordApp = Nothing
    End Sub
    'bookmark stuff
    Public Shared Sub WriteBookmark(ByVal bookmarkString As String, ByVal text As String, ByVal doc As Word.Document)
        Dim bookMark As Word.Bookmark = doc.Bookmarks(bookmarkString)
        Dim rng As Word.Range = bookMark.Range
        rng.Text = text
    End Sub

    Public Shared Sub exportToExcel(ByVal file As String, ByVal startupPath As String, ByRef dbh As DatabaseHelper)
        'get orders
        Dim list As Dictionary(Of Integer, Order) = Order.GetOrdersFromDb(dbh)

        'add extension
        If System.IO.Path.GetExtension(file) <> ".xlsx" Then
            file += ".xlsx"
        End If

        'start excel
        Dim ExcelApp As Excel.Application
        ExcelApp = New Excel.Application
        'don't show excel
        ExcelApp.Visible = False
        'Don't display allerts or errors
        ExcelApp.DisplayAlerts = False

        'new workbook
        Dim workbook As Excel.Workbook = ExcelApp.Workbooks.Add

        'variable for the active worksheet
        Dim worksheet As Excel.Worksheet

        'delete all worksheet except the first one
        Do While ExcelApp.Worksheets.Count > 1
            worksheet = CType(ExcelApp.Worksheets.Item(ExcelApp.Worksheets.Count), Excel.Worksheet)
            worksheet.Delete()
        Loop

        'select the first worksheet
        worksheet = CType(ExcelApp.Worksheets.Item(1), Excel.Worksheet)
        worksheet.Name = "Bestellingen"

        'Fill titles
        worksheet.Range("A1").Value = "Lijst met nog niet betaalde bestellingen"
        worksheet.Range("A2").Value = "Bestel Nummer"
        worksheet.Range("B2").Value = "Datum"
        worksheet.Range("C2").Value = "Eigenaar"
        worksheet.Range("D2").Value = "Totaalbedrag"
        worksheet.Range("E2").Value = "Totaal aantal producten"

        'Font settings
        worksheet.Range("A2:E2").Font.Bold = True
        worksheet.Range("A1").Font.Size = 22

        'starting range
        Dim rng As Excel.Range = worksheet.Range("A3")

        For Each theOrder As Order In list.Values
            If Not theOrder.Paid Then
                rng.Value = theOrder.Id
                rng.Offset(0, 1).Value = turnDateAround(theOrder.CreationDate)
                Dim pers As Person = Person.GetPersonFromDatabase(dbh, theOrder.PerId)
                rng.Offset(0, 2).Value = theOrder.PersonName & " - " & pers.Adreslijn1 & " " & pers.Adreslijn2 & ", " & pers.Postcode & " " & pers.Plaats
                rng.Offset(0, 3).Value = theOrder.TotalPrice
                rng.Offset(0, 4).Value = theOrder.Products.Count
                'next row
                rng = rng.Offset(1, 0)
            End If
        Next

        'save
        workbook.SaveAs(file)

        'close excel
        workbook.Close()
        workbook = Nothing

        ExcelApp.Quit()
        ExcelApp = Nothing
    End Sub

    Public Shared Function turnDateAround(ByVal aDate As String) As String
        Return aDate.Split(CChar("-"))(2) & "-" & aDate.Split(CChar("-"))(1) & "-" & aDate.Split(CChar("-"))(0)
    End Function

End Class
